package com.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import com.bean.EmployeeBean;
import com.exception.LMSException;

/**
 * The Class EmployeeDAO.
 * 
 * @author : gbs02399 Created on : Jul 6, 2012 Project : LMS File Name :
 *         EmployeeDAO.java
 */
public class EmployeeDAO {

	/** The Constant leaveTypeMap. */
	private static final Map<Integer, String> leaveTypeMap = new TreeMap<Integer, String>();
	static {
		leaveTypeMap.put(1, "CL");
		leaveTypeMap.put(2, "SL");
		leaveTypeMap.put(3, "EL");
	}

	/**
	 * Gets the e mployee bean.
	 * 
	 * @param userName
	 *            the user name
	 * @param password
	 *            the password
	 * @return the e mployee bean
	 * @throws LMSException
	 *             the lMS exception
	 */
	public static EmployeeBean getEmployeeBean(final String userName,
			final String password) throws LMSException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		EmployeeBean empBean = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			connection = DriverManager.getConnection(
					"jdbc:oracle:thin:@127.0.0.1:1521:xe", "system","pass");
			String query = "select ed.E_ID,ed.E_NAME,ed.E_USERNAME,ed.E_PASSWORD,ed.E_ROLE from EMPLOYEE_DETAILS ed where ed.E_USERNAME = ?";
			if (password != null) {
				query = query.concat(" and ed.E_PASSWORD = ?");
			}
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, userName);
			if (password != null) {
				preparedStatement.setString(2, password);
			}
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				empBean = new EmployeeBean();
				empBean.setEmpId(Long.valueOf(resultSet.getString("E_ID")));
				empBean.setEmpName(resultSet.getString("E_NAME"));
				empBean.setUserName(resultSet.getString("E_USERNAME"));
				empBean.setEmpPassword(resultSet.getString("E_PASSWORD"));
				empBean.setEmpRole(resultSet.getString("E_ROLE"));
			}
		} catch (final SQLException e) {
			e.printStackTrace();
			throw new LMSException(e.getMessage());
		} catch (final ClassNotFoundException e) {
			e.printStackTrace();
			throw new LMSException(e.getMessage());
		} finally {
			ConnectionCloser.closeConnection(resultSet, connection,
					preparedStatement);
		}
		return empBean;
	}
	
	public static EmployeeBean getEmployeeBean(final String empId) throws LMSException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		EmployeeBean empBean = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			connection = DriverManager.getConnection(
					"jdbc:oracle:thin:@127.0.0.1:1521:xe", "system","pass");
			String query = "select ed.E_ID,ed.E_NAME,ed.E_USERNAME,ed.E_PASSWORD,ed.E_ROLE from EMPLOYEE_DETAILS ed where ed.E_ID = ?";
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, Long.valueOf(empId));
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				empBean = new EmployeeBean();
				empBean.setEmpId(Long.valueOf(resultSet.getString("E_ID")));
				empBean.setEmpName(resultSet.getString("E_NAME"));
				empBean.setUserName(resultSet.getString("E_USERNAME"));
				empBean.setEmpPassword(resultSet.getString("E_PASSWORD"));
				empBean.setEmpRole(resultSet.getString("E_ROLE"));
			}
		} catch (final SQLException e) {
			e.printStackTrace();
			throw new LMSException(e.getMessage());
		} catch (final ClassNotFoundException e) {
			e.printStackTrace();
			throw new LMSException(e.getMessage());
		} finally {
			ConnectionCloser.closeConnection(resultSet, connection,
					preparedStatement);
		}
		return empBean;
	}

	/**
	 * List of employees.
	 * 
	 * @return the list
	 * @throws LMSException
	 *             the lMS exception
	 */
	public static List<EmployeeBean> listOfEmployees() throws LMSException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		final List<EmployeeBean> empList = new ArrayList<EmployeeBean>();
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			connection = DriverManager.getConnection(
					"jdbc:oracle:thin:@127.0.0.1:1521:xe", "system",
					"pass");
			preparedStatement = connection
					.prepareStatement("select ed.E_ID,ed.E_NAME,ed.E_USERNAME,ed.E_PASSWORD,ed.E_ROLE from EMPLOYEE_DETAILS ed");
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				final EmployeeBean employeeBean = new EmployeeBean();
				employeeBean
						.setEmpId(Long.valueOf(resultSet.getString("E_ID")));
				employeeBean.setEmpName(resultSet.getString("E_NAME"));
				employeeBean.setEmpPassword(resultSet.getString("E_USERNAME"));
				employeeBean.setEmpRole(resultSet.getString("E_PASSWORD"));
				empList.add(employeeBean);
			}
		} catch (final SQLException e) {
			e.printStackTrace();
			throw new LMSException(e.getMessage());
		} catch (final ClassNotFoundException e) {
			e.printStackTrace();
			throw new LMSException(e.getMessage());
		} finally {
			ConnectionCloser.closeConnection(resultSet, connection,
					preparedStatement);
		}
		return empList;
	}

	/**
	 * Insert employee.
	 * 
	 * @param employeeBean
	 *            the employee bean
	 * @throws LMSException
	 *             the lMS exception
	 */
	public static void insertEmployee(final EmployeeBean employeeBean)
			throws LMSException {
		Connection con = null;
		final Connection connection = null;
		PreparedStatement preparedStatement = null;
		Statement statement = null;
		ResultSet resultSet = null;
		Integer employeeId = null;
		try {

			Class.forName("oracle.jdbc.driver.OracleDriver");
			con = DriverManager.getConnection(
					"jdbc:oracle:thin:@127.0.0.1:1521:xe", "system",
					"pass");
			statement = con.createStatement();
			resultSet = statement
					.executeQuery("select EMP_SEQ.nextval from dual");
			while (resultSet.next()) {
				employeeId = resultSet.getInt(1);
			}
			preparedStatement = con
					.prepareStatement("insert into employee_details values (?,?,?,?,?)");
			preparedStatement.setInt(1, employeeId);
			preparedStatement.setString(2, employeeBean.getEmpName());
			preparedStatement.setString(3, employeeBean.getUserName());
			preparedStatement.setString(4, employeeBean.getEmpPassword());
			preparedStatement.setString(5, employeeBean.getEmpRole());
			preparedStatement.executeUpdate();
			for (int i = 1; i <= 3; i++) {
				preparedStatement = con
						.prepareStatement("insert into LEAVE_INFO values (LEAVE_INFO_SEQ.nextval,?,?,?,?)");
				preparedStatement.setInt(1, employeeId);
				preparedStatement.setString(2, leaveTypeMap.get(i));
				if (employeeBean.getEmpRole().equals("M")) {
					preparedStatement.setString(3, "15");
				} else {
					preparedStatement.setString(3, "10");
				}
				final Calendar cal = Calendar.getInstance();
				final int year = cal.get(Calendar.YEAR);
				preparedStatement.setString(4, String.valueOf(year));
				preparedStatement.executeUpdate();
			}

		} catch (final SQLException e) {
			e.printStackTrace();
			throw new LMSException(e.getMessage());
		} catch (final ClassNotFoundException e) {
			e.printStackTrace();
			throw new LMSException(e.getMessage());
		} finally {
			ConnectionCloser.closeConnection(null, connection,
					preparedStatement);
		}
	}
	/*public static void main(String[] args) throws Exception{
		Class.forName("oracle.jdbc.driver.OracleDriver");
		System.out.println("got connection");
		Connection con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe","system","pass");
		System.out.println("Connected Successfully To Oracle");
		
		EmployeeBean employeeBean = EmployeeDAO.getEmployeeBean("sandy", "pass");
		System.out.println(employeeBean.toString());
	}*/
}